// 简单的内存数据库实现，用于开发环境
export interface QueryResult {
  rows: any[];
  rowCount: number;
}

export interface Pool {
  query: (text: string, params?: any[]) => Promise<QueryResult>;
  end: () => Promise<void>;
}

// 内存数据库实现
class MemoryDatabase implements Pool {
  private data: Map<string, any[]> = new Map();
  
  constructor() {
    // 初始化表结构
    this.data.set('users', []);
    this.data.set('games', []);
    this.data.set('game_players', []);
    this.data.set('game_states', []);
    this.data.set('player_hands', []);
    this.data.set('game_actions', []);
    
    console.log('内存数据库已初始化');
  }
  
  async query(text: string, params: any[] = []): Promise<QueryResult> {
    // 简单的SQL解析器，仅支持基本操作
    const trimmedText = text.trim().toLowerCase();
    
    // 处理CREATE TABLE语句
    if (trimmedText.startsWith('create table')) {
      console.log('CREATE TABLE 操作已跳过（内存数据库已初始化）');
      return { rows: [], rowCount: 0 };
    }
    
    // 处理CREATE TRIGGER语句
    if (trimmedText.startsWith('create trigger')) {
      console.log('CREATE TRIGGER 操作已跳过（内存数据库不支持触发器）');
      return { rows: [], rowCount: 0 };
    }
    
    // 处理CREATE FUNCTION语句
    if (trimmedText.startsWith('create or replace function')) {
      console.log('CREATE FUNCTION 操作已跳过（内存数据库不支持函数）');
      return { rows: [], rowCount: 0 };
    }
    
    // 处理INSERT语句
    if (trimmedText.startsWith('insert into')) {
      return this.handleInsert(text, params);
    }
    
    // 处理SELECT语句
    if (trimmedText.startsWith('select')) {
      return this.handleSelect(text, params);
    }
    
    // 处理UPDATE语句
    if (trimmedText.startsWith('update')) {
      return this.handleUpdate(text, params);
    }
    
    // 处理DELETE语句
    if (trimmedText.startsWith('delete from')) {
      return this.handleDelete(text, params);
    }
    
    // 默认返回空结果
    console.log('未处理的SQL语句:', text);
    return { rows: [], rowCount: 0 };
  }
  
  private handleInsert(text: string, params: any[]): QueryResult {
    // 简化的INSERT处理
    console.log('处理INSERT语句:', text);
    console.log('参数:', params);
    
    const tableNameMatch = text.match(/insert into (\w+)/i);
    if (!tableNameMatch) return { rows: [], rowCount: 0 };
    
    const tableName = tableNameMatch[1].toLowerCase();
    const table = this.data.get(tableName);
    if (!table) return { rows: [], rowCount: 0 };
    
    // 创建一个简单的记录
    const record: any = {};
    
    // 获取列名 - 使用更简单的方法，直接查找括号之间的内容
    const insertStart = text.toLowerCase().indexOf('insert into ' + tableName);
    const valuesStart = text.toLowerCase().indexOf('values');
    
    if (insertStart !== -1 && valuesStart !== -1) {
      // 提取列名部分
      const columnsPart = text.substring(insertStart + `insert into ${tableName}`.length, valuesStart);
      const columnsMatch = columnsPart.match(/\((.*?)\)/);
      
      if (columnsMatch) {
        const columns = columnsMatch[1].split(',').map(c => c.trim());
        console.log('列名:', columns);
        
        // 处理VALUES部分
        const valuesMatch = text.match(/values \((.*?)\)/i);
        if (valuesMatch) {
          const valuesPart = valuesMatch[1];
          console.log('VALUES部分:', valuesPart);
          
          // 检查是否是参数化查询（包含$1, $2等）
          if (valuesPart.includes('$')) {
            // 参数化查询处理 - 直接按顺序映射参数
            console.log('使用参数化查询处理');
            console.log('参数数组长度:', params.length);
            console.log('列名数组长度:', columns.length);
            
            columns.forEach((col, index) => {
              console.log(`处理列 ${col}, 索引 ${index}, 参数值:`, params[index]);
              if (params[index] !== undefined) {
                record[col] = params[index];
                console.log(`设置 ${col} = ${params[index]}`);
              } else {
                console.log(`参数 ${index} 未定义`);
              }
            });
          } else {
            // 非参数化查询处理
            const values = valuesPart.split(',').map(v => v.trim().replace(/'/g, ''));
            columns.forEach((col, index) => {
              record[col] = values[index];
            });
          }
        }
      }
    }
    
    // 添加自动生成的ID和时间戳
    if (!record.id) {
      record.id = table.length + 1;
    }
    if (!record.created_at) {
      record.created_at = new Date().toISOString();
    }
    if (!record.updated_at) {
      record.updated_at = new Date().toISOString();
    }
    
    table.push(record);
    console.log(`插入记录到 ${tableName}:`, record);
    
    return { rows: [record], rowCount: 1 };
  }
  
  private handleSelect(text: string, params: any[]): QueryResult {
    // 简化的SELECT处理
    const tableNameMatch = text.match(/from (\w+)/i);
    if (!tableNameMatch) return { rows: [], rowCount: 0 };
    
    const tableName = tableNameMatch[1].toLowerCase();
    const table = this.data.get(tableName);
    if (!table) return { rows: [], rowCount: 0 };
    
    // 处理WHERE条件
    let result = [...table];
    const whereMatch = text.match(/where (.+?)(?: order by| limit|$)/i);
    if (whereMatch) {
      const whereClause = whereMatch[1];
      
      // 简单的等式条件处理
      const equalMatches = whereClause.matchAll(/(\w+)\s*=\s*'(.*?)'/g);
      for (const match of equalMatches) {
        const column = match[1];
        const value = match[2];
        result = result.filter(row => row[column] === value);
      }
      
      // 处理参数化查询
      const paramMatches = whereClause.matchAll(/(\w+)\s*=\s*\$/g);
      let paramIndex = 0;
      for (const match of paramMatches) {
        const column = match[1];
        if (params[paramIndex]) {
          result = result.filter(row => row[column] === params[paramIndex]);
          paramIndex++;
        }
      }
    }
    
    // 处理ORDER BY
    const orderByMatch = text.match(/order by (\w+)/i);
    if (orderByMatch) {
      const column = orderByMatch[1];
      result.sort((a, b) => {
        if (a[column] < b[column]) return -1;
        if (a[column] > b[column]) return 1;
        return 0;
      });
    }
    
    // 处理LIMIT
    const limitMatch = text.match(/limit (\d+)/i);
    if (limitMatch) {
      const limit = parseInt(limitMatch[1]);
      result = result.slice(0, limit);
    }
    
    return { rows: result, rowCount: result.length };
  }
  
  private handleUpdate(text: string, params: any[]): QueryResult {
    // 简化的UPDATE处理
    const tableNameMatch = text.match(/update (\w+)/i);
    if (!tableNameMatch) return { rows: [], rowCount: 0 };
    
    const tableName = tableNameMatch[1].toLowerCase();
    const table = this.data.get(tableName);
    if (!table) return { rows: [], rowCount: 0 };
    
    // 处理SET部分
    const setMatch = text.match(/set (.+?) where/i);
    if (!setMatch) return { rows: [], rowCount: 0 };
    
    const setClause = setMatch[1];
    const updates: { [key: string]: any } = {};
    
    // 处理等式更新
    const equalMatches = setClause.matchAll(/(\w+)\s*=\s*'(.*?)'/g);
    for (const match of equalMatches) {
      const column = match[1];
      const value = match[2];
      updates[column] = value;
    }
    
    // 处理参数化更新
    const paramMatches = setClause.matchAll(/(\w+)\s*=\s*\$/g);
    let paramIndex = 0;
    for (const match of paramMatches) {
      const column = match[1];
      if (params[paramIndex]) {
        updates[column] = params[paramIndex];
        paramIndex++;
      }
    }
    
    // 添加updated_at
    updates.updated_at = new Date().toISOString();
    
    // 处理WHERE条件
    const whereMatch = text.match(/where (.+)/i);
    if (!whereMatch) return { rows: [], rowCount: 0 };
    
    const whereClause = whereMatch[1];
    let updatedCount = 0;
    
    // 简单的等式条件处理
    const whereEqualMatches = whereClause.matchAll(/(\w+)\s*=\s*'(.*?)'/g);
    for (const match of whereEqualMatches) {
      const column = match[1];
      const value = match[2];
      
      for (const row of table) {
        if (row[column] === value) {
          Object.assign(row, updates);
          updatedCount++;
        }
      }
    }
    
    // 处理参数化WHERE条件
    const whereParamMatches = whereClause.matchAll(/(\w+)\s*=\s*\$/g);
    for (const match of whereParamMatches) {
      const column = match[1];
      if (params[paramIndex]) {
        for (const row of table) {
          if (row[column] === params[paramIndex]) {
            Object.assign(row, updates);
            updatedCount++;
          }
        }
        paramIndex++;
      }
    }
    
    return { rows: [], rowCount: updatedCount };
  }
  
  private handleDelete(text: string, params: any[]): QueryResult {
    // 简化的DELETE处理
    const tableNameMatch = text.match(/delete from (\w+)/i);
    if (!tableNameMatch) return { rows: [], rowCount: 0 };
    
    const tableName = tableNameMatch[1].toLowerCase();
    const table = this.data.get(tableName);
    if (!table) return { rows: [], rowCount: 0 };
    
    // 处理WHERE条件
    const whereMatch = text.match(/where (.+)/i);
    if (!whereMatch) {
      // 如果没有WHERE条件，清空表
      const count = table.length;
      table.length = 0;
      return { rows: [], rowCount: count };
    }
    
    const whereClause = whereMatch[1];
    const originalLength = table.length;
    
    // 简单的等式条件处理
    const equalMatches = whereClause.matchAll(/(\w+)\s*=\s*'(.*?)'/g);
    for (const match of equalMatches) {
      const column = match[1];
      const value = match[2];
      
      for (let i = table.length - 1; i >= 0; i--) {
        if (table[i][column] === value) {
          table.splice(i, 1);
        }
      }
    }
    
    // 处理参数化WHERE条件
    const paramMatches = whereClause.matchAll(/(\w+)\s*=\s*\$/g);
    let paramIndex = 0;
    for (const match of paramMatches) {
      const column = match[1];
      if (params[paramIndex]) {
        for (let i = table.length - 1; i >= 0; i--) {
          if (table[i][column] === params[paramIndex]) {
            table.splice(i, 1);
          }
        }
        paramIndex++;
      }
    }
    
    return { rows: [], rowCount: originalLength - table.length };
  }
  
  async end(): Promise<void> {
    console.log('内存数据库连接已关闭');
  }
}

// 创建内存数据库实例
const pool = new MemoryDatabase();

export default pool;